﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace DAL
{
   public class ClientUserDAL
    {

       public DataTable getUserDataSource()
       {
           string sql = "select ac_no,e_name from customer where e_name is not null";
           return GetDataTable(sql);


       }
       public bool isOkUser(string username, string paw)
       {
           bool isok = false;

           string sql = "select ac_no from customer where ac_no='" + username + "'and passwd='" + paw + "'";
           SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.SQLConnectionString, CommandType.Text, sql);
           while (dr.Read())
           {
               if (dr[0].ToString() != "")
               {
                   isok = true;
               }
           }

           return isok;
       }

      



       public DataSet  writerxmlinfo(string acno)
       {

           DataSet ds = new DataSet();

           string sql = "SELECT SpName,Shop_name,ac_group,spred    from  itemsprinfo  left join moneydiffinfo on moneydiffinfo.spid=itemsprinfo.spred_group " +
           " left join GROUPINFO on itemsprinfo.groupid=groupinfo.group_id left join shopinfo on  shopinfo.shop_id=itemsprinfo.itemcode    where groupid in (SELECT GROUP_ID FROM GROUPINFO where GROUP_ID in (select group_no from customer where ac_no='" + acno + "'))";

           //string sql = "select * from v_diffMoney where Groupid in (select group_no from customer where ac_no='" + acno + "')";
           ds = GetDataSet(sql);
           return ds;          
       
       }

       public DataSet getGroupInfoByID()
       {

           DataSet ds = new DataSet();
           string sql = "select group_id,ac_group from GroupInfo where (f_id is null) and g_flag=0";
           ds = GetDataSet(sql);
           return ds;

       }



       public DataSet getUserGroupInfo()
       {

           DataSet ds = new DataSet();
           string sql = "select group_id,ac_group from GroupInfo where (f_id is null) and  g_flag=0";
           ds = GetDataSet(sql);
           return ds;

       }
       public DataSet getUserInfoByGroup(string groupid)
       {

           DataSet ds = new DataSet();
           string sql = "select c_name from customer where group_no='" + groupid + "'";
           ds = GetDataSet(sql);
           return ds;

       }

       public  DataSet GetDataSet(string StrSQL)
       {
           DataSet dstemp = new DataSet();
           dstemp = SqlHelper.ExecuteDataSet(SqlHelper.SQLConnectionString, CommandType.Text, StrSQL, null);
           return dstemp;
       }
       public DataTable GetDataTable(string strSQL)
       {
           DataTable dttemp = null;
           try
           {
               dttemp = SqlHelper.ExecuteDatetable(SqlHelper.SQLConnectionString, strSQL);

           }
           catch (Exception ex)
           {
               ex.GetBaseException();

           }
           return dttemp;
       }


       public int AddUser(string acno,string aeno,string cname,string ename,string password,double balance)
       {

           int number = 0;
           try
           {
              
               string sql = "insert into customer(ac_no,ae_no,c_name,e_name,passwd,balance) values('" + acno + "','" + aeno + "','" + cname + "','" + ename + "','" + password + "'," + balance + ")";

               number = SqlHelper.ExecuteNonQuery(SqlHelper.SQLConnectionString, CommandType.Text, sql, null);

           }
           catch (Exception ex)
           { 
               ex.GetBaseException(); 
           } 
           return number;
       }


       //得到用户组数据信息allyn
       public DataSet getDataSetGoupChild()
       {
           string sql = " select * from GroupInfo";
           return GetDataSet(sql);
       }


       public int updateUserGroup(string oldid, string oldname, string cname, string ename, string password, double balance)
       {

           int number = 0;
           try
           {

               string sql = "";
               number = SqlHelper.ExecuteNonQuery(SqlHelper.SQLConnectionString, CommandType.Text, sql, null);

           }
           catch (Exception ex)
           {
               ex.GetBaseException();
           }
           return number;
       }

    }
}
